package com.mjduan.myBank.myPayment.dao.daoImpl;

import java.text.DecimalFormat;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.hibernate4.HibernateTemplate;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import com.mjduan.myBank.myPayment.dao.IMyPayDao;
import com.mjduan.myBank.util.entity.MyPay;
import com.mjduan.myBank.util.entity.User;
import com.mjduan.myBank.util.tableEnum.EnumTables;
import com.mjduan.myBank.util.tableEnum.MyPayEnum;

@Component("MyPayDaoImpl")
@Transactional(rollbackFor = Exception.class)
public class MyPayDaoImpl implements IMyPayDao {
    @Autowired
    private HibernateTemplate hibernateTemplate;

    @Override
    public long getCount() {
        String hql = "select count(*) from MyPay";
        return ((Long) hibernateTemplate.iterate(hql).next()).longValue();
    }

    @Override
    public String getSumOfMoney() {
        String hql = "select sum(amountOfMoney) from MyPay";
        double result = ((Double) hibernateTemplate.iterate(hql).next()).doubleValue();
        //对结果保留俩位小数
        return new DecimalFormat("#.00").format(result);
    }

    @Override
    public boolean addPay(MyPay myPay) {
        long id = (long) hibernateTemplate.save(myPay);
        return id > 0;
    }

    /**
     * 返回值不会为null
     */
    @Override
    public List<MyPay> getMyPayWithLimit(int offset, int rows) {
        String hql = "from MyPay order by id desc";
        Object[] parameters = {offset, rows};
        Query sqlQuery = hibernateTemplate.getSessionFactory().getCurrentSession().createQuery(hql);
        List list = sqlQuery.setFirstResult((offset - 1) * rows).setMaxResults(rows).list();
        return list;
    }

    public boolean addPayFromExcel(final List<Object[]> list, final User user) {
        if (list.isEmpty()) {
            return true;
        }

        StringBuffer sql = new StringBuffer();
        sql.append("insert into " + EnumTables.t_mypay);
        sql.append("(" + MyPayEnum.amountOfMoney + "," + MyPayEnum.remark + "," + MyPayEnum.user +
                "," + MyPayEnum.time + ")");
        sql.append(" values(?,?,?,?)");

//        this.jdbcTemplate.batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
//            public void setValues(PreparedStatement arg0, int arg1) throws SQLException {
//                arg0.setObject(1, list.get(arg1)[0]);
//                arg0.setObject(2, list.get(arg1)[1]);
//                arg0.setObject(3, user.getId());
//                arg0.setObject(4, list.get(arg1)[2]);
//            }
//
//            public int getBatchSize() {
//                // TODO Auto-generated method stub
//                return list.size();
//            }
//        });

        return true;
    }

    public boolean addPayFromExcel(List<MyPay> myPayList) {
        int buck = 100;
        Session currentSession = hibernateTemplate.getSessionFactory().getCurrentSession();
        Transaction transaction = currentSession.beginTransaction();
        for (int i = 0; i < myPayList.size(); i++) {
            currentSession.save(myPayList.get(i));
            if (i % buck == 0) {
                currentSession.flush();
                currentSession.clear();
            }
        }
        transaction.commit();
        currentSession.close();
        return true;
    }

    @Override
    public List<MyPay> getAllMyPayWithoutRemark(User user) {
        String hql = "from MyPay m,User u where m.user=u.id and u.id=? order by m.id desc";
        List<MyPay> myPays = (List<MyPay>) hibernateTemplate.find(hql, (long) user.getId());
        return myPays;
    }
}
